/*****************************************************************************
 *   Copyright 2005 Tim A Wang
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 *
 ****************************************************************************/
package com.jb.web.bean.database;

import com.jb.database.metamodel.Column;
import com.jb.database.metamodel.Database;
import com.jb.database.metamodel.Schema;
import com.jb.database.metamodel.Table;
import com.jb.faces.context.FacesContext;
import com.jb.framework.DataSourceManager;
import com.jb.framework.PersistentContext;
import com.jb.framework.database.Predicate;
import com.jb.framework.database.UpdateDBCommand;
import com.jb.util.ClassUtil;
import com.jb.util.StringUtil;
import com.jb.web.application.Wizard;
import com.jb.web.application.configuration.Config;
import com.jb.web.bean.Button;
import com.jb.web.bean.CheckBox;
import com.jb.web.bean.DateTextField;
import com.jb.web.bean.Form;
import com.jb.web.bean.FormField;
import com.jb.web.bean.Label;
import com.jb.web.bean.ListBox;
import com.jb.web.bean.TableCell;
import com.jb.web.bean.TableRow;
import com.jb.web.bean.TextArea;
import com.jb.web.bean.TextField;
import com.jb.web.bean.event.UIEvent;
import com.jb.web.bean.inspector.OptionPropertyEditor;
import com.jb.web.context.WebContext;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


/**
 * Form for database record.
 */
public class DatabaseRecord extends Form {
    static final long serialVersionUID = 123123L;
    static final private String NONE_PERSISTENT_PREFIX = "nb.";

    public static class DRWizard extends Wizard
    {
        public DRWizard() {
            this.setType(DatabaseRecord.class.getName());
            this.setPageName("dbListWizard");
            this.setPanelName("dataWizards");
        }
        
        public void create(WebContext ctx, UIEvent event) {
            super.create(ctx, event);
            //Form form =(Form) event.getSource();
            Database db = ctx.getDatabase();
            Schema schema = db.getSchema();
            Table table = schema.getTable();
            /*
            List list = table.getColumns();
            List selected = new ArrayList();
            Iterator iter = list.iterator();
            
            while(iter.hasNext()) {
                Column column = (Column) iter.next();
                String param = event.get(column.getName());
                if(param!=null) {
                    selected.add(column.getName());
                }
            }*/
            Form form =(Form) event.getSource();
            ListBox select = (ListBox)form.getFormField("selectedColumns");
            if(select==null) {
                ctx.addErrorMessage(this, "Can not find filed selectedColumns");
                return;
            }
            List selected = select.getSelected();
           
            DatabaseRecord dr = (DatabaseRecord)this.getObject();//created, we need to set
             String clientId = event.get("name");
             if(clientId!=null && clientId.length()>0)
                 dr.setClientId(clientId);
            
            dr.setSchemaName(schema.getName());
            dr.setTableName(table.getName());
            
            dr.generateForm(ctx, selected);
        }
    }
    
    static {
        publish(DatabaseRecord.class, GENERAL, "predicate");
        publish(DatabaseRecord.class, GENERAL, "tableName");
        publish(DatabaseRecord.class, GENERAL, "schemaName");
        publish(DatabaseRecord.class, GENERAL, "generateForm");
        publish(DatabaseRecord.class, GENERAL, "beanClass");
        publish(DatabaseRecord.class, GENERAL, "recordAction",
            new OptionPropertyEditor("static:query,Query;update,Update;insert,Insert"));
        registerWizard(DatabaseRecord.class, DatabaseRecord.DRWizard.class);
    }

    public static String INSERT = "insert";
    public static String DELETE = "delete";
    public static String UPDATE = "update";
    public static String QUERY = "query";
    public static String BROWSE = "browse";
    private List currentSet;
    private DatabaseTable queryDataSet;
    private String beanClass;
    private String predicate;

    // private static Log log = LogFactory.getLog(getClass().getName());
    private String recordAction = QUERY;
    private String schemaName;
    private String tableName;
    private Table table; //meta data
    private boolean generateForm;
    private boolean _generateForm;

    public void setBeanClass(String beanClass) {
        this.beanClass = beanClass;
    }

    public String getBeanClass() {
        return beanClass;
    }

    public void setGenerateForm(boolean generateForm) {
        _generateForm= generateForm;
        this.generateForm = false;
    }

    public boolean isGenerateForm() {
        return generateForm;
    }

    /**
     * Sets predicate for this record
     *
     * @param predicate DOCUMENT ME!
     */
    public void setPredicate(String predicate) {
        this.predicate = predicate;
    }

    /**
     * Returns predicate set for this record.
     *
     * @return predicate.
     */
    public String getPredicate() {
        return predicate;
    }

    public void setRecordAction(String recordAction) {
        this.recordAction = recordAction;

        if (QUERY.equals(recordAction)) {
            resetForm();
        }

        if (INSERT.equals(recordAction)) {
            resetForm();
        }
    }

    public String getRecordAction() {
        return recordAction;
    }

    /**
     * Sets schema name.
     *
     * @param schemaName schema name for this record.
     */
    public void setSchemaName(String schemaName) {
        this.schemaName = schemaName;
    }

    /**
     * Returns schema name set for this record.
     *
     * @return schema name.
     */
    public String getSchemaName() {
        return schemaName;
    }

    /**
     * Sets db table name
     * @param tableName table name
     */
    public void setTableName(String tableName) {
        if ((tableName != null) && !tableName.equals(this.tableName)) {
            this.tableName = tableName;
        }
    }

    /**
     * Returns table name.
     *
     * @return table name.
     */
    public String getFromClause() {
        if(this.schemaName!=null && schemaName.length()>0)
        return schemaName+"."+tableName;
        else
        return tableName;
    }

    public String getTableName() {
        return tableName;
    }

    public void edit(String id) {
        setRecordAction(QUERY);

        FormField field = getFormField("id");
        field.setValue(id);
    } 
    
    /**
     * Process updates.
     *
     * @param ctx calling context.
     **/
    public void processUpdates(FacesContext ctx) {
        super.processUpdates(ctx);
        WebContext webCtx =(WebContext) ctx; 
        if (isMyEvent(webCtx)) {
            try {
                if (this.recordAction.equals(INSERT)) {
                    insert(webCtx);
                } else if (this.recordAction.equals(UPDATE)) {
                    update(webCtx);
                } else if (this.recordAction.equals(DELETE)) {
                    delete(webCtx);
                } else if (this.recordAction.equals(QUERY)) {
                    query(webCtx);
                } else if (this.recordAction.equals(BROWSE)) {
                    browseAction(webCtx);
                    browse(webCtx);
                }
            } catch (SQLException e) {
                webCtx.addErrorMessage(this, "Failed to update database record "+e.getMessage());
                webCtx.renderResponse();
            }
        }
    }

    private Connection getConnection() throws SQLException {
        return DataSourceManager.getConnection(this);
    }

    /**
     * Retruns table definition.
     *
     * @return Table definition.
     */
    private Table getMetaData() throws SQLException {
        if ((table == null) || !table.getName().equals(tableName)) {
            Database db = new Database();

            if ((schemaName != null) && (schemaName.length() > 0)) {
                db.setSchemaName(schemaName);
                table = db.getSchema().getTable(tableName);
            } else {
                db.setSchemaName(Config.getInstance().getDBConfig().getSchema());
                table = db.getSchema().getTable(tableName);
            }
        }

        return table;
    }

    public Class getModelClass() {
        return ClassUtil.getClass(beanClass);
    }

    public void browse(WebContext ctx) throws SQLException {
        if (queryDataSet == null) {
            return;
        }

        currentSet = (List) queryDataSet.getSelectedItem();

        if (currentSet == null) {
            ctx.addErrorMessage(this,"No data found");
            return;
        }
        
        Iterator fields = getFields().iterator();
        int i = 0;

        while (fields.hasNext()) {
            FormField ff = (FormField) fields.next();

            if (!(ff instanceof Button) && !ff.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                ff.setModelBean(currentSet.get(i));
               
                i++;
            }
        }
    }

    private void browseAction(WebContext ctx) {
        if (queryDataSet == null) {
            ctx.addErrorMessage(this, "Query is not performed!");
            return; //does nothing
        }

        queryDataSet.getNext();
    }

    private void delete(WebContext ctx) {
        ctx.addErrorMessage(this, "Delete function not implemented");
        StringBuffer deleteStmt = new StringBuffer("delete from " + this.getFromClause() + " where id='");
        FormField field = this.getFormField("id");
        deleteStmt.append(field.getDecodedValue());
        deleteStmt.append("'");
    }

    /**
    * Generates form from table structure.
    * @param ctx
    */
    void generateForm(WebContext ctx) {

        Table table = null;

        try {
            table = getMetaData();
        } catch (SQLException e) {
            ctx.addErrorMessage(this, e.getMessage());

            return;
        }

        if (table == null) {
            ctx.addErrorMessage(this, "Failed to find table " + getFromClause());

            return;
        }

        if (table.getColumns() == null) {
            ctx.addErrorMessage(this, "No columns defined for table " + getFromClause());

            return;
        }
        _generateForm = false;

        this.clearChildren();
        Iterator iter = table.getColumns().iterator();
        setColumns(1);
        setRows(table.getColumns().size() + 1);

        valueChanged(ctx);

        int i = 0;

        while (iter.hasNext()) {
            Column col = (Column) iter.next();
            TableRow row = getRow(i++);
            TableCell cell = row.getCellAt(0);

            Label label = new Label();

            label.setValue(StringUtil.itemNameToLabel(col.getName()));
            label.setDisplayStyle("block");
            label.setStyleClass("fieldLabel");

            FormField field = null;

            if ("text".equals(col.getTypeName())) {
                field = (FormField) ClassUtil.createInstance(TextArea.class);
                field.setWidth("300px");
                field.setHeight("50px");
            } else if ("datetime".equals(col.getTypeName())) {
                field = (FormField) ClassUtil.createInstance(DateTextField.class);
                field.setRendererType("com.jb.web.renderer.DateTimeFieldRenderer");
                field.setDefaultValue("${session.time}");
            } else if ("date".equals(col.getTypeName())) {
                field = (FormField) ClassUtil.createInstance(DateTextField.class);
                field.setRendererType("com.jb.web.renderer.DateFieldRenderer");
                field.setDefaultValue("${session.time}");
            } else if ("boolean".equals(col.getTypeName())) {
                field = (FormField) ClassUtil.createInstance(CheckBox.class);
            } else {
                field = (FormField) ClassUtil.createInstance(TextField.class);
            }

            field.setDefault(ctx);
            field.setClientId(col.getName());
            cell.addChild(label);
            cell.addChild(field);
        }

        TableRow row = getRow(i++);
        row.setCols(1);

        TableCell cell = row.getCellAt(0);

        Button field = (Button) ClassUtil.createInstance(Button.class);
        field.setLabel("${" + this.getClientId() + ".recordAction=='browse'?'Next':resource.submit}");
      //  field.setFormAction("submit");
        field.setOnClick("JB.submitForm('" + this.getClientId() + "','" + getParent().getClientId(ctx) + "')");
        cell.addChild(field);

        field = (Button) ClassUtil.createInstance(Button.class);
        field.setLabel("Query");
        field.setOnClick("JB.setProperty('" + this.getClientId() + "','recordAction','query','" +
            getParent().getClientId(ctx) + "')");
        cell.addChild(field);
/*
        field = (Button) ClassUtil.createInstance(Button.class);
        field.setLabel("Update");
        field.setOnClick("JB.setProperty('" + this.getClientId() + "','recordAction','update','" +
            getParent().getClientId(ctx) + "')");
        cell.addChild(field);

        field = (Button) ClassUtil.createInstance(Button.class);
        field.setLabel("New");
        field.setOnClick("JB.setProperty('" + this.getClientId() + "','recordAction','insert','" +
            getParent().getClientId(ctx) + "')");
        cell.addChild(field);
*/
    }

    void generateForm(WebContext ctx, List list) {

     Table table = null;

     try {
         table = getMetaData();
     } catch (SQLException e) {
         ctx.addErrorMessage(this, e.getMessage());

         return;
     }

     if (table == null) {
         ctx.addErrorMessage(this, "Failed to find table " + getFromClause());

         return;
     }

     if (table.getColumns() == null) {
         ctx.addErrorMessage(this, "No columns defined for table " + getFromClause());

         return;
     }
     _generateForm = false;

     this.clearChildren();
     Iterator iter = list.iterator();//.getColumns().iterator();
     setColumns(1);
     setRows(list.size() + 1);

     valueChanged(ctx);

     int i = 0;

     while (iter.hasNext()) {
         String colName=(String)iter.next();
         
         Column col = (Column)table.getChildByTypeAndName(Column.class, colName);
            
         TableRow row = getRow(i++);
         TableCell cell = row.getCellAt(0);

         Label label = new Label();

         label.setValue(StringUtil.itemNameToLabel(col.getName()));
         label.setDisplayStyle("block");
         label.setStyleClass("fieldLabel");

         FormField field = null;

         if ("text".equals(col.getTypeName())) {
             field = (FormField) ClassUtil.createInstance(TextArea.class);
             field.setWidth("300px");
             field.setHeight("50px");
         } else if ("datetime".equals(col.getTypeName())) {
             field = (FormField) ClassUtil.createInstance(DateTextField.class);
             field.setRendererType("com.jb.web.renderer.DateFieldRenderer");
           //  field.setDefaultValue("${session.time}");
         } else if ("date".equals(col.getTypeName())) {
             field = (FormField) ClassUtil.createInstance(DateTextField.class);
             field.setRendererType("com.jb.web.renderer.DateFieldRenderer");
            // field.setDefaultValue("${session.time}");
         } else if ("boolean".equals(col.getTypeName())) {
             field = (FormField) ClassUtil.createInstance(CheckBox.class);
         } else {
             field = (FormField) ClassUtil.createInstance(TextField.class);
         }

         field.setDefault(ctx);
         field.setClientId(col.getName());
         cell.addChild(label);
         cell.addChild(field);
     }

     TableRow row = getRow(i++);
     row.setCols(1);

     TableCell cell = row.getCellAt(0);

     Button field = (Button) ClassUtil.createInstance(Button.class);
     field.setLabel("${" + this.getClientId() + ".recordAction=='browse'?'Next':resource.submit}");
   //  field.setFormAction("submit");
     field.setOnClick("JB.submitForm('" + this.getClientId() + "','" + getParent().getClientId(ctx) + "')");
     cell.addChild(field);

     field = (Button) ClassUtil.createInstance(Button.class);
     field.setLabel("Query");
     field.setOnClick("JB.setProperty('" + this.getClientId() + "','recordAction','query','" +
     getParent().getClientId(ctx) + "')");
     cell.addChild(field);
 }
    
    private void insert(WebContext ctx) throws SQLException {
        
        Iterator fields = getFields().iterator();
        StringBuffer insertStmt = new StringBuffer("insert into " + getFromClause() + "(");
        String valueStmt = "";
        List valueList = new ArrayList();

        while (fields.hasNext()) {
            FormField ff = (FormField) fields.next();

            if (!(ff instanceof Button) && !ff.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                if (valueList.size() > 0) {
                    insertStmt.append(",");
                    valueStmt += ",";
                }

                insertStmt.append(ff.getFieldName());
                valueStmt += "?";

                valueList.add(ff.getDecodedValue());
                ff.setDecodedValue(null);
            }
        }

        if (valueStmt.endsWith(",")) {
            valueStmt = valueStmt.substring(0, valueStmt.length() - 1);
        }

        insertStmt.append(")");
        insertStmt.append(" values (" + valueStmt + ")");
        doSql(insertStmt.toString(), valueList);
    }
    
    public boolean valueChanged(PersistentContext ctx) {
        if (_generateForm) {
            this.generateForm((WebContext)ctx);
        }
        return super.valueChanged(ctx);
    }
    
    private void doSql(String insertStmt, List valueList)
        throws SQLException {
        Connection conn = null;

        try {
            conn = getConnection();

            UpdateDBCommand s = new UpdateDBCommand(insertStmt);
            s.execute(valueList, conn);
            conn.commit();
        }catch(Exception e){
            if(log.isErrorEnabled())
                log.error(this + " insertRecord ", e);
            conn.rollback();    
        }finally {
            if (conn != null) {
                DataSourceManager.releaseConnection(this, conn);
            }
        }
    }

    private void query(WebContext ctx) throws SQLException {
        Iterator fields = getFields().iterator();
        StringBuffer selectStmt = new StringBuffer("select ");
        int i = 0;
        currentSet = null;

        while (fields.hasNext()) {
            FormField ff = (FormField) fields.next();

            if (!(ff instanceof Button) && !ff.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                if (i > 0) {
                    selectStmt.append(",");
                }
                selectStmt.append(ff.getFieldName());
                i++;
            }
        }

        selectStmt.append(" from " + getFromClause());
        queryDataSet = new DatabaseTable();
        queryDataSet.setSelectStatement(selectStmt.toString());
        queryDataSet.setFetchSize(200);
        queryDataSet.setBlockSize(200);
        queryDataSet.setPredicate(queryPredicate());
        List list = queryDataSet.getList((WebContext) ctx);
        
        if(list==null ||list.size()==0) {
            ctx.addErrorMessage(this, "No data found");
            return;
        }
        recordAction = BROWSE;
        browse(ctx);
    }
    
    private Predicate queryPredicate() {
        Iterator iter = getFields().iterator();
        Predicate predicate = null;
        int i = 0;
        while (iter.hasNext()) {
            FormField item = (FormField) iter.next();

            if (!item.isHidden() && !(item instanceof Button) &&
                    !item.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                    
                Object value = item.getDecodedValue();

                if (value != null) {
                    if (value instanceof String) {
                        String strValue = value.toString();

                        if (strValue.length() == 0) {
                            continue;
                        }
                        Predicate newPre = new Predicate(item.getFieldName() + " like '" + strValue + "'");
                        if(predicate!=null)
                            predicate = predicate.AND(newPre);
                        else
                            predicate = newPre;

                    } else {
                        //Predicate newPre = new Predicate(item.getFieldName() + " = '" + value + "'");

                        Predicate newPre = new Predicate(item.getFieldName(), value ,"=");
                        if(predicate!=null)
                            predicate = predicate.AND(newPre);
                        else
                            predicate = newPre;

                    }
                    i++;
                }
            }
        }
        if(log.isDebugEnabled()) {
            log.debug(this.getClass().getName() + ".queryPredicate query with " + predicate);
        }
        return predicate;
    }
    
    private String toQuery() {
        Iterator iter = getFields().iterator();
        StringBuffer predicate = new StringBuffer();
        int i = 0;

        while (iter.hasNext()) {
            FormField item = (FormField) iter.next();

            if (!item.isHidden() && !(item instanceof Button) &&
                    !item.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                Object value = item.getDecodedValue();

                if (value != null) {
                    if (value instanceof String) {
                        String strValue = value.toString();

                        if (strValue.length() == 0) {
                            continue;
                        }

                        if (i > 0) {
                            predicate.append(" AND ");
                        }

                        predicate.append(item.getFieldName() + " like '" + strValue + "'");
                    } else {
                        if (i > 0) {
                            predicate.append(" AND ");
                        }

                        predicate.append(item.getFieldName() + "='" + value + "'");
                    }

                    i++;
                }
            }
        }
        if(log.isDebugEnabled()) {
            log.debug(this.getClass().getName() + ".toQuery query with " + predicate);
        }
        return predicate.toString();
    }

    private void update(FacesContext ctx) throws SQLException {
        Iterator it = getFields().iterator();

        StringBuffer insertStmt = new StringBuffer("update " + getFromClause() + " set ");
        List valueList = new ArrayList();

        String id = "";

        while (it.hasNext()) {
            FormField ff = (FormField) it.next();

            if (!(ff instanceof Button) && !ff.getFieldName().startsWith(NONE_PERSISTENT_PREFIX)) {
                insertStmt.append(ff.getFieldName() + " = ?");
                valueList.add(ff.getDecodedValue());

                if (ff.getFieldName().equals("id")) {
                    id = (String) ff.getDecodedValue();
                }

                if (it.hasNext()) {
                    insertStmt.append(",");
                }
            }
        }

        if (insertStmt.charAt(insertStmt.length() - 1) == ',') {
            insertStmt.setCharAt(insertStmt.length() - 1, ' ');
        }

        insertStmt.append(" where id = '" + id + "'");
        doSql(insertStmt.toString(), valueList);
    }

    private void updateWithResultSet(ResultSet rs) {
        try {
            Iterator it = this.getFields().iterator();

            while (it.hasNext()) {
                FormField field = (FormField) it.next();

                if (field instanceof Button) {
                    continue;
                }

                String fName = field.getName();

                try {
                    Object pValue = rs.getObject(fName);
                    field.setValue(pValue.toString());
                } catch (Exception e) {
                    if(log.isErrorEnabled())
                        log.error(this, e);
                }
            }
        } catch (Exception e) {
            if(log.isErrorEnabled())
                log.error(this, e);
        }
    }
}
